﻿/****************************** Module Header ******************************\
 * Module Name:  EntityDBHelper.cs
 * Module Version:  1.0.0 (2012-4-25)
 * Copyright (c) AnyEB.Com (14507247@qq.com)
 * 
 * This class is used to collections DbParameter. It supplies the useful method
 * of add DbParameter.
 * 
 * log:2012-11-29 by sutroon
 * 重构,由DALDBHelper改为EntityDBHelper
 * 
 * This source is subject to the GPL License.
 * See http://www.gnu.org/licenses/gpl.html.
 * All other rights reserved.
 * 
 * THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, 
 * EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED 
 * WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
\***************************************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.Common;
using System.Reflection;
using System.Text;
using System.Configuration;
using UCF.Core.Attribute;

namespace UCF.Core.Data.ORM
{
    /// <summary>
    /// 数据实体对象数据库帮助类
    /// </summary>
    public class EntityDBHelper
    {
        #region 私有字段
        /// <summary>
        /// DBHelper实例对象
        /// </summary>
        private DatabaseHelper dbh;

        /// <summary>
        /// DbCommand实例对象
        /// </summary>
        private DbCommand cmd;

        /// <summary>
        /// 数据表主键名称
        /// </summary>
        private string primaryKey;

        /// <summary>
        /// 数据表排序键名称
        /// </summary>
        private string sortKey;

        /// <summary>
        /// 配置文件数据库连接信息
        /// <para>[0]=ConnectionName,[1]=ProviderName</para>
        /// </summary>
        string connectionString;
        string providerName;
        #endregion

        /// <summary>
        /// 构造方法
        /// </summary>
        /// <param name="primaryKey">主键名称</param>
        /// <param name="primaryKey">主排序键名称</param>
        /// <param name="connectionInfo"></param>
            public EntityDBHelper(string primaryKey, string sortKey, string connectionString,string providerName)
        {
            this.connectionString = connectionString;
            this.providerName = providerName;
            this.dbh = new DatabaseHelper(connectionString, providerName);
            this.cmd = dbh.GetDbCommand();
            this.primaryKey = primaryKey;
            this.sortKey = sortKey;
        }

        #region 增删改查

        /// <summary>
        /// 添加实体对象对应的数据库记录,返回所影响的行数
        /// </summary>
        /// <param name="e"></param>
        /// <returns></returns>
        public int AddEntity<T>(T e)
        {
            IList<DbParameter> lstParas = new List<DbParameter>();
            StringBuilder sbFields = new StringBuilder();
            StringBuilder sbValues = new StringBuilder();
            Type type = typeof(T);
            object oVal;
            DBColumnAttribute attrCol;
            DbParameter para;
            foreach (PropertyInfo pi in type.GetProperties())
            {
                oVal = pi.GetValue(e, null);
                if (oVal == null)
                {
                    if (pi.Name == this.primaryKey && (pi.PropertyType == typeof(int?) || pi.PropertyType == typeof(int))) continue; // 跳过自增长类型的主键字段
                    foreach (System.Attribute attr in pi.GetCustomAttributes(false))
                    {
                        attrCol = attr as DBColumnAttribute;
                        if (attrCol == null) break;
                        oVal = attrCol.DefaultValue;
                    }
                }
                if (oVal == null) continue;
                if ((pi.PropertyType == typeof(DateTime?) || pi.PropertyType == typeof(DateTime)) && oVal.ToString() == "Now") oVal = DateTime.Now;
                if ((pi.PropertyType == typeof(bool?) || pi.PropertyType == typeof(bool)))
                {
                    if (oVal.ToString() == "0") oVal = false;
                    if (oVal.ToString() == "1") oVal = true;
                }
                if ((pi.PropertyType == typeof(Guid?) || pi.PropertyType == typeof(Guid)))
                {
                    if (pi.Name == this.primaryKey && (oVal == null || (Guid)oVal == Guid.Empty)) oVal = Guid.NewGuid();
                    if (oVal.ToString() == "new()") oVal = Guid.NewGuid();
                    if (oVal.ToString() == "default") oVal = Guid.Empty;
                }
                sbFields.AppendFormat(" [{0}],", pi.Name);
                sbValues.AppendFormat(" @{0},", pi.Name);
                para = this.cmd.CreateParameter();
                para.ParameterName = "@{0}".Fmt(pi.Name);
                para.DbType = DatabaseHelper.ClrTypeToDbType(pi.PropertyType);
                para.Value = oVal;
                lstParas.Add(para);
            }
            string sql = "insert into [{0}] ({1}) values ({2})".Fmt(type.Name, sbFields.ToString().TrimEnd(','), sbValues.ToString().TrimEnd(','));
            return this.dbh.ExecuteNonQuery(sql, lstParas.ToArray());
        }

        /// <summary>
        /// 添加实体对象对应的数据库记录,返回 该记录的主键值
        /// </summary>
        /// <param name="e"></param>
        /// <param name="lastRecordQueryCondition">获取最后一条语句的命令</param>
        /// <returns></returns>
        public object InsertEntity<T>(T e, string lastRecordQueryCondition = "") where T : class,new()
        {
            this.AddEntity(e);
            T t = this.GetLastEntity<T>(lastRecordQueryCondition);
            return typeof(T).GetProperty(this.primaryKey).GetValue(t, null);
        }

        /// <summary>
        /// 更新实体对象对应的数据库记录
        /// </summary>
        /// <param name="e"></param>
        /// <param name="this.primaryKey"></param>
        /// <returns></returns>
        public int UpdateEntity<T>(T e,string condition="")
        {
            IList<DbParameter> lstParas = new List<DbParameter>();
            StringBuilder sb = new StringBuilder();
            Type type = typeof(T);
            object oVal;
            DbParameter para;
            foreach (PropertyInfo pi in type.GetProperties())
            {
                oVal = pi.GetValue(e, null);
                if (oVal == null) continue;
                if (condition.Length==0 && pi.Name == this.primaryKey)
                {
                    condition = this.SetPrimaryKeyCondition(pi.PropertyType, pi.Name, oVal);
                    continue;
                }
                sb.AppendFormat(" [{0}]=@{0},", pi.Name);
                para = this.cmd.CreateParameter();
                para.ParameterName = "@{0}".Fmt(pi.Name);
                para.DbType = DatabaseHelper.ClrTypeToDbType(pi.PropertyType);
                para.Value = oVal;
                lstParas.Add(para);
            }
            if (condition.Length == 0) throw new Exception("Where条件未设置");
            if (sb.Length == 0) return 0;
            string sql = "update [{0}] set {1} where {2}".Fmt(type.Name, sb.ToString().TrimEnd(','), condition);
            return this.dbh.ExecuteNonQuery(sql, lstParas.ToArray());
        }

        /// <summary>
        /// 更新局部字段
        /// </summary>
        /// <param name="id"></param>
        /// <param name="namevaluesql"></param>
        /// <returns></returns>
        public int UpdateEntity<T>(object id, string namevaluesql)
        {
            Type type = typeof(T);
            PropertyInfo pi = type.GetProperty(this.primaryKey);
            if (pi == null) return 0;
            string sql = "update [{0}] set {1} where {2}".Fmt(type.Name, namevaluesql, this.SetPrimaryKeyCondition(pi.PropertyType, this.primaryKey, id));
            return this.dbh.ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 批量更新数据库记录
        /// </summary>
        /// <param name="namevaluesql"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int UpdateEntity<T>(string namevaluesql, string condition)
        {
            string sql = "update [{0}] set {1} {2}".Fmt(typeof(T).Name, namevaluesql, DatabaseHelper.Sql_SetConditionText(condition));
            return this.dbh.ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 删除数据库记录,返回所影响的行数
        /// </summary>
        /// <param name="e"></param>
        /// <param name="this.primaryKey"></param>
        /// <returns></returns>
        public int DeleteEntityById<T>(object id)
        {
            Type type = typeof(T);
            PropertyInfo pi = type.GetProperty(this.primaryKey);
            if (pi == null) return 0;
            string sql = "";
            if (pi.PropertyType == typeof(int?) || pi.PropertyType == typeof(int))
            {
                sql = "delete from [{0}] where {1}={2}".Fmt(type.Name, this.primaryKey, id);
            }
            else
            {
                sql = "delete from [{0}] where {1}='{2}'".Fmt(type.Name, this.primaryKey, id);
            }
            return this.dbh.ExecuteNonQuery(sql);
        }

        /// <summary>
        /// 批量删除项目
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int DeleteEntities<T>(string condition)
        {
            return this.dbh.ExecuteNonQuery("delete from [{0}] where {1}".Fmt(typeof(T).Name, condition));
        }

        /// <summary>
        /// 获取实体对象列表
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="count">记录数量，0=不限</param>
        /// <returns></returns>
        public IList<T> GetEntityList<T>(string condition = "", int count = 0) where T : new()
        {
            IList<T> list = new List<T>();
            T t;
            using (IDataReader dr = this.dbh.ExecuteDataTable(this.GetSelectSqlStatement<T>(condition, count)).CreateDataReader())
            {
                while (dr.Read())
                {
                    t = new T();
                    EntityHelper.EntityFromDataReader<T>(ref t, dr);
                    list.Add(t);
                }
            }
            return list;
        }

        public IList<T> GetEntityPagingList<T>(string where,string orderBy, int pageid, int pagesize) where T : new()
        {
            IList<T> list = new List<T>();
            T t;
            using (IDataReader dr = this.dbh.ExecuteDataTable(this.GetSelectPagingSqlStatement<T>(where,orderBy, pageid, pagesize )).CreateDataReader())
            {
                while (dr.Read())
                {
                    t = new T();
                    EntityHelper.EntityFromDataReader<T>(ref t, dr);
                    list.Add(t);
                }
            }
            return list;
        }

        public DataTable GetEntityPagingTable<T>(string where, string orderBy, int pageid, int pagesize) where T : new()
        {
            return this.dbh.ExecuteDataTable(this.GetSelectPagingSqlStatement<T>(where, orderBy, pageid, pagesize));
        }
        /// <summary>
        /// 获取指定字段值的列表
        /// </summary>
        /// <typeparam name="V"></typeparam>
        /// <param name="v"></param>
        /// <param name="fieldName"></param>
        /// <param name="condition"></param>
        /// <returns></returns>
        public virtual IList<T> GetEntityListByFieldValue<T, V>(object v, string fieldName, string condition = "") where T : class,new()
        {
            string sql = "{0}={1}".Fmt(fieldName, (typeof(T) == typeof(int)) ? v : "'{0}'".Fmt(v));
            if (condition.Length > 0)
            {
                string sKey = condition.GetFirstWord().ToLower();
                if (sKey == "order")
                {
                    sql = "{0} {1}".Fmt(sql, condition);
                }
                else
                {
                    sql = "{0} and {1}".Fmt(sql, condition);
                }
            }
            return this.GetEntityList<T>(sql);
        }

        /// <summary>
        /// 获取指定主键的实体对象
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public T GetEntityById<T>(object id) where T : class,new()
        {
            PropertyInfo pi = typeof(T).GetProperty(this.primaryKey);
            if (pi == null) return default(T);
            return this.GetEntity<T>(this.SetPrimaryKeyCondition(pi.PropertyType, this.primaryKey, id));
        }

        /// <summary>
        /// 获取指定查询条件的实体对象
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="paras">可选的参数化查询参数</param>
        /// <returns></returns>
        public T GetEntity<T>(string condition, params DbParameter[] paras) where T : class,new()
        {
            T t = null;
            using (IDataReader dr = this.dbh.ExecuteDataTable(this.GetSelectSqlStatement<T>(condition, 1), paras).CreateDataReader())
            {
                while (dr.Read())
                {
                    t = new T();
                    EntityHelper.EntityFromDataReader<T>(ref t, dr);
                }
            }
            return t;
        }

        /// <summary>
        /// 查询数量
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public int GetEntityCount<T>(string condition, params DbParameter[] paras)
        {
            int i = condition.ToLower().IndexOf(" order by ");
            if (i > -1) condition = condition.Substring(0, i);
            string sql = "select count(0) from [{0}] {1}".Fmt(typeof(T).Name, DatabaseHelper.Sql_SetConditionText(condition));
            return this.dbh.ExecuteDataTable(sql, paras).Rows[0][0].ToString().ToInt();
        }

        /// <summary>
        /// 检测指定条件的记录是否存在
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public bool ExistsEntity<T>(string condition)
        {
            return this.GetEntityCount<T>(condition) > 0;
        }

        /// <summary>
        /// 获取最后一条记录的实体对象
        /// </summary>
        /// <param name="lastRecordQueryCondition"></param>
        /// <returns></returns>
        public T GetLastEntity<T>(string lastRecordQueryCondition = "") where T : class,new()
        {
            if (lastRecordQueryCondition.Length == 0) lastRecordQueryCondition = "order by [{0}] desc".Fmt(this.sortKey);
            lastRecordQueryCondition = this.GetSelectSqlStatement<T>(lastRecordQueryCondition, 1);
            PropertyInfo pi = typeof(T).GetProperty(this.primaryKey);
            if (pi == null) return default(T);
            T t = null;
            using (IDataReader dr = this.dbh.ExecuteDataTable(lastRecordQueryCondition).CreateDataReader())
            {
                while (dr.Read())
                {
                    t = new T();
                    EntityHelper.EntityFromDataReader<T>(ref t, dr);
                }
            }
            return t;
        }

        /// <summary>
        /// 获取符合条件的所有主键的值的列表
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public IList<V> GetIDsList<T, V>(string condition, string field = "")
        {
            string sql = "select {0} from {1} {2}".Fmt(string.IsNullOrEmpty(field) ? this.primaryKey : field, typeof(T).Name, DatabaseHelper.Sql_SetConditionText(condition));
            return this.dbh.GetColumnValues<V>(sql);
        }

        #endregion

        #region 其他数据库交互方法

        /// <summary>
        /// 创建实体对象对应的数据表
        /// </summary>
        public void CreateTableForEntity<T>()
        {
            Type type = typeof(T);
            DBTableAttribute attrTb;
            foreach (System.Attribute attr in type.GetCustomAttributes(false))
            {
                attrTb = attr as DBTableAttribute;
                if (attrTb == null) break;
                if (!attrTb.Disabled) return; // 如果实体类指示Enable=false则退出数据库创建
            }

            StringBuilder sb = new StringBuilder();
            string typeName;
            string strAutoIncrement = DatabaseHelper.Sql_GetAutoIncrementText();
            DBColumnAttribute attrCol;
            string intLength = "255";
            foreach (PropertyInfo pi in type.GetProperties())
            {
                typeName = DatabaseHelper.ClrTypeToDbTypeName(pi.PropertyType);
                if (pi.Name == this.primaryKey)
                {
                    switch (typeName)
                    {
                        case "varchar":
                            sb.AppendFormat(" {0} {1}({2}) primary key,", pi.Name, typeName, "255");
                            break;
                        case "uniqueidentifier":
                            sb.AppendFormat(" {0} {1} not null,", pi.Name, typeName);
                            break;
                        default:
                            sb.AppendFormat(" {0} {1} primary key {2},", pi.Name, typeName, strAutoIncrement);
                            break;
                    }
                    continue;
                }
                if (typeName == "varchar")
                {
                    foreach (System.Attribute oAttr in pi.GetCustomAttributes(false))
                    {
                        attrCol = oAttr as DBColumnAttribute;
                        if (attrCol != null)
                        {
                            if (attrCol.DbType != null && (attrCol.DbType.ToLower() == "text" || attrCol.DbType.ToLower() == "xml"))
                            {
                                typeName = attrCol.DbType.ToLower();
                                break;
                            }
                            if (attrCol.Length != null)
                            {
                                intLength = attrCol.Length;
                                break;
                            }
                        }
                    }
                    if (typeName == "text" || typeName == "xml")
                    {
                        sb.AppendFormat(" {0} {1},", pi.Name, typeName);
                        continue;
                    }
                    sb.AppendFormat(" {0} {1}({2}),", pi.Name, typeName, intLength);
                    continue;
                }
                sb.AppendFormat(" {0} {1},", pi.Name, typeName);

            }
            string sql = "create table [{0}] ({1})".Fmt(type.Name, sb.ToString().TrimEnd(','));
            this.dbh.ExecuteNonQuery(sql);
        }
        #endregion

        #region 存储过程操作方法
        /// <summary>
        /// 获取指定存储过程的实体对象
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        public T GetEntityByProc<T>(string procedureName, params System.Data.Common.DbParameter[] dbParameters) where T : class,new()
        {
            T t = null;
            using (IDataReader dr = dbh.ExecuteProcedureDataTable(procedureName, dbParameters).CreateDataReader())
            {
                while (dr.Read())
                {
                    t = new T();
                    EntityHelper.EntityFromDataReader<T>(ref t, dr);
                }
            }
            return t;
        }

        /// <summary>
        /// 获取指定存储过程的实体对象列表
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="dbParameters"></param>
        /// <returns></returns>
        public IList<T> GetEntityListByProc<T>(string procedureName, params System.Data.Common.DbParameter[] dbParameters) where T : new()
        {
            IList<T> list = new List<T>();
            using (IDataReader dr = dbh.ExecuteProcedureDataTable(procedureName, dbParameters).CreateDataReader())
            {
                T t;
                while (dr.Read())
                {
                    t = new T();
                    EntityHelper.EntityFromDataReader<T>(ref t, dr);
                    list.Add(t);
                }
            }
            return list;
        }
        #endregion

        #region 与实体对象无关的方法

        /// <summary>
        /// 获取查询语句
        /// </summary>
        /// <param name="condition"></param>
        /// <param name="count">记录数量，如果是0则表示不限</param>
        /// <returns></returns>
        public string GetSelectSqlStatement<T>(string condition, int count = 0)
        {
            if (condition.Length > 12 && condition.TrimStart().ToLower().Substring(0, 7) == "select ") return condition;
            StringBuilder sb = new StringBuilder();
            Type type = typeof(T);
            foreach (PropertyInfo pi in type.GetProperties())
            {
                sb.AppendFormat(" [{0}],", pi.Name);
            }
            if (count > 0)
            {
                return DatabaseHelper.Sql_GetSelectTopNRecordText(type.Name, sb.ToString().TrimEnd(','), DatabaseHelper.Sql_SetConditionText(condition), count);
            }
            return "select {0} from {1} {2}".Fmt(sb.ToString().TrimEnd(','), type.Name, DatabaseHelper.Sql_SetConditionText(condition));
        }

        /// <summary>
        /// 获取分页查询语句
        /// <para>2012-10-16</para>
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <param name="orderby"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public string GetSelectPagingSqlStatement<T>(string condition,string orderby, int pageIndex, int pageSize)
        {
            if (condition.Length > 12 && condition.TrimStart().ToLower().Substring(0, 7) == "select ") return condition;
            StringBuilder sb = new StringBuilder();
            Type type = typeof(T);
            foreach (PropertyInfo pi in type.GetProperties())
            {
                sb.AppendFormat(" [{0}],", pi.Name);
            }
            if (pageIndex == 1) return "select top {3} {0} from {1} {2}".Fmt(sb.ToString().TrimEnd(','), type.Name, DatabaseHelper.Sql_SetSqlCondition(condition,orderby),pageSize);
            return "select {2} from(select row_number() over (order by {5}) as row_num,{2} from {3} {4}) as page_table where row_num between {0} and {1};".Fmt((pageIndex - 1) * pageSize + 1, pageIndex * pageSize, sb.ToString().TrimEnd(','), type.Name, DatabaseHelper.Sql_SetSqlCondition(condition, ""), orderby);
        }

        /// <summary>
        /// 设置主键查询条件
        /// </summary>
        /// <param name="propertyType"></param>
        /// <param name="k"></param>
        /// <param name="v"></param>
        /// <returns></returns>
        private string SetPrimaryKeyCondition(Type propertyType, string k, object v)
        {
            if (propertyType == typeof(int?) || propertyType == typeof(int))
            {
                return "{0}={1}".Fmt(k, v);
            }
            return "{0}='{1}'".Fmt(k, v);
        }

        /// <summary>
        /// 获取数据表对象
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public DataTable GetTable<T>(string condition = "")
        {
            DataTable dt = this.dbh.ExecuteDataTable(this.GetSelectSqlStatement<T>(condition));
            dt.TableName = typeof(T).Name;
            return dt;
        }

        /// <summary>
        /// 获取IDataReader数据集对象
        /// </summary>
        /// <param name="condition"></param>
        /// <returns></returns>
        public IDataReader GetReader<T>(string condition = "")
        {
            return this.dbh.ExecuteDataTable(this.GetSelectSqlStatement<T>(condition)).CreateDataReader();
        }
        #endregion


        public DataSet GetDataSet<T>(string sql, string[] fields)
        {
            throw new NotImplementedException();
        }
    }
}
